CREATE PROC [dbo].[CopyBasicDrugInformation]
    @OriginalDrugCode VARCHAR(15),
    @DestinationDrugCode VARCHAR(15)
AS
BEGIN
    ---------------Copy Basic Drug Information
    SET NOCOUNT ON;

    DECLARE @StatusCode SMALLINT = 0;
    DECLARE @StatusMsg NVARCHAR(100);

    BEGIN TRY
        BEGIN TRAN;

        SET @StatusCode = 100;
        SET @StatusMsg = N'       ';

        IF @OriginalDrugCode = @DestinationDrugCode
        BEGIN
            RAISERROR(@StatusMsg, 16, 1);
        END;


        SET @StatusCode = 101;
        SET @StatusMsg = N'     ';

        IF NOT EXISTS (SELECT 1 FROM dbo.KalaId WHERE K_Code = @OriginalDrugCode)
            RAISERROR(@StatusMsg, 16, 1);

        SET @StatusCode = 102;
        SET @StatusMsg = N'     ';

        IF NOT EXISTS
        (
            SELECT 1
            FROM dbo.KalaId
            WHERE K_Code = @DestinationDrugCode
        )
            RAISERROR(@StatusMsg, 16, 1);


        SET @StatusCode = 103;
        SET @StatusMsg = N'       ';


        IF NOT EXISTS
        (
            SELECT TOP (1)
                   1
            FROM dbo.Anbar
            WHERE K_Code = @OriginalDrugCode
        )
            RAISERROR(@StatusMsg, 16, 1);

        SET @StatusCode = 104;
        SET @StatusMsg = N'       ';

        IF NOT EXISTS
        (
            SELECT TOP (1)
                   1
            FROM dbo.Anbar
            WHERE K_Code = @DestinationDrugCode
        )
            RAISERROR(@StatusMsg, 16, 1);

        ----------------------------------------------------------- KalaId

        SET @StatusCode = 105;
        SET @StatusMsg = N'     ';

        UPDATE Destination
        SET Destination.Meli_Code = OriginalInfo.Meli_Code,
            Destination.Brand_Code = OriginalInfo.Brand_Code,
            Destination.Org_Code = OriginalInfo.Org_Code,
            Destination.EquipmentCode = OriginalInfo.EquipmentCode,
            Destination.Hamrah_Flag = OriginalInfo.Hamrah_Flag,
            Destination.Serial_flag = OriginalInfo.Serial_flag,
            Destination.Exp_Flag = OriginalInfo.Exp_Flag,
            Destination.Point_Flag = OriginalInfo.Point_Flag,
            Destination.Darou_Flag = OriginalInfo.Darou_Flag,
            Destination.Make_Flag = OriginalInfo.Make_Flag,
            Destination.CtrMojodi = OriginalInfo.CtrMojodi,
            Destination.Parvande_Flag = OriginalInfo.Parvande_Flag,
            Destination.U_Code1 = OriginalInfo.U_Code1,
            Destination.U_Code2 = OriginalInfo.U_Code2,
            Destination.Gid_Fk = OriginalInfo.Gid_Fk,
            Destination.Gid_Fk1 = OriginalInfo.Gid_Fk1,
            Destination.Gid_Fk2 = OriginalInfo.Gid_Fk2,
            Destination.R_Unit1_2 = OriginalInfo.R_Unit1_2
        FROM dbo.KalaId Destination
            CROSS APPLY
        (
            SELECT K_Code,
                   Meli_Code,
                   Brand_Code,
                   Org_Code,
                   EquipmentCode,
                   Hamrah_Flag,
                   Serial_flag,
                   Exp_Flag,
                   Point_Flag,
                   Darou_Flag,
                   Make_Flag,
                   CtrMojodi,
                   Parvande_Flag,
                   U_Code1,
                   U_Code2,
                   U_Code3,
                   Gid_Fk,
                   Gid_Fk1,
                   Gid_Fk2,
                   R_Unit1_2
            FROM dbo.KalaId
            WHERE K_Code = @OriginalDrugCode
        ) AS OriginalInfo
        WHERE Destination.K_Code = @DestinationDrugCode;
        ----------------------------------------------------------- Along Goods
        DELETE FROM dbo.Hamrah_Kala
        WHERE K_Code = @DestinationDrugCode;

        IF EXISTS (SELECT 1 FROM dbo.Hamrah_Kala WHERE K_Code = @OriginalDrugCode)
        BEGIN
            SET @StatusCode = 106;
            SET @StatusMsg = N'     ';


            INSERT INTO dbo.Hamrah_Kala
            (
                K_Code,
                K_Code_H,
                K_Qty1,
                EditQty_Flag,
                Qty_K_Code,
                A_Code_H,
                Price_K_Code_H,
                Note
            )
            SELECT @DestinationDrugCode,
                   K_Code_H,
                   K_Qty1,
                   EditQty_Flag,
                   Qty_K_Code,
                   A_Code_H,
                   Price_K_Code_H,
                   Note
            FROM dbo.Hamrah_Kala
            WHERE K_Code = @OriginalDrugCode;
        END;

        ----------------------------------------------------------- Price Sazman
        DELETE FROM dbo.Price_Sazman
        WHERE K_Code = @DestinationDrugCode;

        IF EXISTS (SELECT 1 FROM dbo.Price_Sazman WHERE K_Code = @OriginalDrugCode)
        BEGIN
            SET @StatusCode = 107;
            SET @StatusMsg = N'     ';



            INSERT INTO dbo.Price_Sazman
            (
                K_Code,
                Sazman_Code,
                Price,
                [Status],
                BimarPercent,
                MessageId,
                AgeLimit
            )
            SELECT @DestinationDrugCode,
                   Sazman_Code,
                   Price,
                   [Status],
                   BimarPercent,
                   MessageId,
                   AgeLimit
            FROM dbo.Price_Sazman
            WHERE K_Code = @OriginalDrugCode;

        END;

        ----------------------------------------------------------- Farmacope Rules
        DELETE FROM dbo.Farmacope
        WHERE K_Code = @DestinationDrugCode;

        IF EXISTS
        (
            SELECT TOP (1)
                   1
            FROM dbo.Farmacope
            WHERE K_Code = @OriginalDrugCode
        )
        BEGIN
            SET @StatusCode = 108;
            SET @StatusMsg = N'     ǘ ';

            INSERT INTO dbo.Farmacope
            (
                K_Code,
                Grade_Code,
                Sazman_Code,
                [Status],
                [Message],
                ShowMessage
            )
            SELECT @DestinationDrugCode,
                   Grade_Code,
                   Sazman_Code,
                   [Status],
                   [Message],
                   ShowMessage
            FROM dbo.Farmacope
            WHERE K_Code = @OriginalDrugCode;
        END;

        ----------------------------------------------------------- Exclusive Insurance Code
        DELETE FROM dbo.ExclusiveInsuranceCode
        WHERE GoodsCode = @DestinationDrugCode;

        IF EXISTS
        (
            SELECT TOP (1)
                   1
            FROM dbo.ExclusiveInsuranceCode
            WHERE GoodsCode = @OriginalDrugCode
        )
        BEGIN
            SET @StatusCode = 109;
            SET @StatusMsg = N'       ';


            INSERT INTO dbo.ExclusiveInsuranceCode
            (
                Id,
                Code,
                GoodsCode,
                InsuranceCode,
                SalesCeiling,
                HasTTAC
            )
            SELECT NEWID(),
                   Code,
                   @DestinationDrugCode,
                   InsuranceCode,
                   SalesCeiling,
                   HasTTAC
            FROM dbo.ExclusiveInsuranceCode
            WHERE GoodsCode = @OriginalDrugCode;
        END;

        ----------------------------------------------------------- CoFamilyCode Insurancce 
        DELETE FROM dbo.CoFamilyCode
        WHERE GoodsCode = @DestinationDrugCode;

        IF EXISTS
        (
            SELECT TOP (1)
                   1
            FROM dbo.CoFamilyCode
            WHERE GoodsCode = @OriginalDrugCode
        )
        BEGIN

            SET @StatusCode = 111;
            SET @StatusMsg = N'       ';


            INSERT INTO dbo.CoFamilyCode
            (
                GoodsCode,
                InsuranceType,
                GenericCode,
                IRCCode,
                CreatedOn
            )
            SELECT @DestinationDrugCode,
                   InsuranceType,
                   GenericCode,
                   IRCCode,
                   GETDATE()
            FROM dbo.CoFamilyCode
            WHERE GoodsCode = @OriginalDrugCode;

        END;
        ----------------------------------------------------------- tblMsg For Every Defined Warehouses
        DELETE FROM dbo.tblMsg
        WHERE K_Code = @DestinationDrugCode;

        IF EXISTS
        (
            SELECT TOP (1)
                   1
            FROM dbo.tblMsg
            WHERE K_Code = @OriginalDrugCode
        )
        BEGIN

            INSERT INTO dbo.tblMsg
            (
                A_Code,
                K_Code,
                msgText,
                msgFlag,
                prnFlag
            )
            SELECT W.A_Code,
                   @DestinationDrugCode,
                   M.msgText,
                   M.msgFlag,
                   M.prnFlag
            FROM dbo.tblMsg M
                JOIN dbo.Anbar W
                    ON W.A_Code = M.A_Code
                       AND W.K_Code = @DestinationDrugCode
            WHERE M.K_Code = @OriginalDrugCode;
        END;
        ----------------------------------------------------------- Copy Warehouse Info
        UPDATE D
        SET D.Saghf_Forosh = O.Saghf_Forosh,
            D.kala_Min = O.kala_Min,
            D.Kala_Max = O.Kala_Max
        FROM dbo.Anbar D
            JOIN dbo.Anbar O
                ON O.A_Code = D.A_Code
                   AND O.K_Code = @OriginalDrugCode
        WHERE D.K_Code = @DestinationDrugCode;
        ----------------------------------------------------------- End of Operaion

        SET @StatusCode = 1;
        SET @StatusMsg = N'     ';

        SELECT @StatusCode StatusCode,
               @StatusMsg StatusMsg;

        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        SELECT @StatusCode StatusCode,
               @StatusMsg StatusMsg;
        PRINT ERROR_MESSAGE();
        ROLLBACK TRAN;
    END CATCH;
END;